import mysql from 'mysql2'
//数据库配置对象
const sqlConfig={
    host:'localhost', // 连接数据库的地址
    user:'root',//用户名
    password:'123456',// 用户密码
    port:3306,//端口号
    database:'myshop'// 要使用的数据库名字
}
//创建连接池
const pool=mysql.createPool(sqlConfig)
// 定义执行sql语句的方法
function exec(sql:string){
    //返回一个 Promiss 对象
    return new Promise((resolve,reject)=>{
        // 从连接池中获取一个连接
        pool.getConnection((err,connect)=>{
            if(err){
                reject(err)
            }else{
                // 执行 SQL 操作
                connect.query(sql,(error,result)=>{
                    if(error){
                        reject(error)
                    }else{
                        resolve({result})
                    }
                })
            }
            //释放连接
            connect?.release()
        })
    })
}
//定义查询数据的方法
//可以指定查询的字段组
let queryData=(table:string, keys:string[],where:string,callback:(data:any)=>void)=>{
    //拼接完整的查询语句
    let keyString = ""
    keys.forEach((key)=>{
        keyString += key + ","
    })
    keyString = keyString.substring(0,keyString.length-1)
    let sql = `select ${keyString} from ${table} where ${where}`
    exec(sql).then(result=>{
        callback(result)
    }).catch(()=>{
        callback(null)
    })
}
//定义插入数据的方法
let insertData=(table:string,keys:string[],values:any[],callback:(data:any)=>void)=>{
    //拼接完整的 SQL 语 句
    let keyString = "("
    keys.forEach((key)=>{
        keyString+=key+","
    })
    keyString=keyString.substring(0,keyString.length-1)
    keyString+=')'
    let valueString="("
    values.forEach((value)=>{
        if(typeof value==='string'){
            valueString+=`'${value}',` 
        }else{
            valueString+=value+","
        }
    })
    valueString=valueString.substring(0,valueString.length-1)
    valueString+=')'
    let sql =`insert into ${table}${keyString} values ${valueString}`
    exec(sql).then(result =>{	
        callback(result)
    }).catch(()=>{
        callback(null)
    })
}
// 更新方法
let updateData = (table:string,updateString:string,where:string)=>{ 
    let sql = `update ${table} set ${updateString} where ${where}`
    exec(sql).then()
}
// 删除方法
let deleteData = (table:string,where:string)=>{ 
    let sql = `delete from ${table} where ${where}`
    exec(sql).then()
}
//联表查询方法
//mainTable: 主表名
//mainTableKeys: 主表要查询的字段
//othersKeys: 二维数组，每个元素为对应副表要查询的字段
//otherTableName: 要查询的副表名数组，与othersKeys顺序对应
//linkKeys: 主表数据中对应的副表的键名数组
//linkValues: 最终要组合进结果数据的属性名数组
let queryDataFrom =(mainTable:string,
    mainTableKeys:string[],
    othersKeys:string[][],
    otherTableName:string[],
    linkKeys:string[],
    linkValues:string[],
    where:string,
    callback:(data:any)=>void)=>{
        // 构建主表查询的字段字符串
        let keyString = ""
        mainTableKeys.forEach((key)=>{
            keyString += key + ","
        })
        keyString = keyString.substring(0,keyString.length-1)
        //构建副表查询的字符串
        let otherKeyString = ""  // 初始化
        let index = 0
        //遍历所有副表
        otherTableName.forEach((table)=>{
            let otherString = ""
            //副表要查询的字段
            let otherKeys = othersKeys[index]
            //副表主键对应在主表中的字段名
            let linkKey = linkKeys[index]
            //副表查询出的数据对应在结果对象中的属性名
            let linkValue = linkValues[index]
            //遍历副表要查询的字段，构建语句
            otherKeys.forEach((otherK)=>{
                if(otherString.length>0){
                    otherString += ','
                }
                otherString += `'${otherK}',${table}.${otherK}`
            })
            if(otherKeyString.length>0){
                otherKeyString += ','
            }
            otherKeyString += `(select json_object(${otherString}) from ${table} ${table} where ${table}.id=o.${linkKey}) as ${linkValue}`
            index++
        })
        // 完整 SQL 语句拼接
        let sql = `select ${keyString},${otherKeyString} from ${mainTable} o where ${where}`
        exec(sql).then(result => {
            callback(result)
        }).catch((error)=>{
            console.log('sqlError:',error);
            callback(null)
        })
    }
//查询某个表中的数据个数
let dataCount = (tableName:string,callback:(data:any)=>void)=>{
    let sql = `SELECT COUNT(*) AS count FROM ${tableName};`
    exec(sql).then((result:any)=>{
        callback(result.result[0].count)
    }).catch((error)=>{
        console.log('sqlError:',error);
        callback(null)
    })
}
//查询最近1日内的数据条数
let dataCountWithinLastday = (tableName:string,dateName:string,callback:(data:any)=>void)=>{
    let sql = `SELECT DATE(${dateName}) AS date ,
    COUNT(*) AS count FROM ${tableName} 
    WHERE ${dateName} >= CURDATE()-INTERVAL 1 DAY
    GROUP  BY  DATE(${dateName}) 
    ORDER   BY   DATE(${dateName})DESC;`
    exec(sql).then((result:any)=>{
        callback(result.result[0].count)
    }).catch((error)=>{
        console.log('sqlError:',error);
        callback(null)
    })
}
//查询最近一月内每日的数据，以日期维度聚合
let datawithinMonth = (tableName:string,dateName:string,callback:(data:any)=>void)=> {
    let sql = `SELECT DATE(${dateName}) AS date , 
    COUNT(*) AS count FROM ${tableName} 
    WHERE ${dateName} >= CURDATE()-INTERVAL 30 DAY 
    GROUP BY DATE(${dateName}) 
    ORDER BY DATE(${dateName}) DESC;`
    exec(sql).then((result:any)=>{
        callback(result.result)
    }).catch((error)=>{
        console.log('sqlError:',error);
        callback(null)
    })
}
//进行数据求和，指定表名和要求和的字段名
let dataSum = (tableName:string,sumName:string,callback:(data:any)=>void)=>{
    let sql = `SELECT SUM(${sumName}) AS total FROM ${tableName}`
    exec(sql).then((result:any)=>{
        callback(result.result[0].total)
    }).catch((error)=>{
        console.log('sqlError:',error);
        callback(null)
    })
}
//进行一个月内每日的数据求和，以日期为维度聚合
let dataSumwithinMonth = (tableName:string,dateName:string,sumName:string,callback:(data:any)=>void)=>{
    let sql = `SELECT DATE(${dateName}) AS date , 
    SUM(${sumName}) AS count FROM ${tableName} 
    WHERE ${dateName}>=CURDATE()-INTERVAL 30 DAY 
    GROUP BY DATE(${dateName}) 
    ORDER BY DATE(${dateName})DESC;`
    exec(sql).then((result:any)=>{
        callback(result.result)
    }).catch((error)=>{
        console.log('sqlError:',error);
        callback(null)
    })
}
//导出工具方法
export default {
    queryData,
    insertData,
    updateData,
    deleteData,
    queryDataFrom,  
    dataCount,     
    dataCountWithinLastday,
    datawithinMonth,
    dataSum,
    dataSumwithinMonth
}